package Dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Entity.Student;
import Entity.Teacher;
import OracleHelper.OracleHelper;

public class TeachersInformationDao {
	
	public Connection getConnection() {
		Connection conn=null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url="jdbc:oracle:thin:@localhost:1521:orcl";
			String user="c##university_resource";
			String pwd="20161946";
			conn = DriverManager.getConnection(url, user, pwd);
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	/*
	 * 向teachersInformation表中添加教师信息
	 */
	public int addTeacher(String teacherNo, String name, String sex, String dept, String teacherId,String pwd,String tel) {
		String sql = "insert into teachersInformation values('" + teacherNo + "','" + name + "','" + sex + "','" + dept
				+ "','" + teacherId + "'," + "'" + pwd + "','" + tel + "')";
		return new OracleHelper().executeUpdate(sql);
	}
	
	/*
	 * 删除教师信息
	 */
	public int deleteTeacherByNo(String teacherNo) {
		String sql = "delete  from teachersInformation where teacherNo='" + teacherNo + "'";
		return new OracleHelper().executeUpdate(sql);
	}
	
	/*
	 * 修改教师密码
	 */
	public int alterPassword(String teacherNo,String newPwd) {
		String sql = "update teachersInformation set pwd='" + newPwd + "' where teacherNo='"+teacherNo+"'";
			return new OracleHelper().executeUpdate(sql);	
	}
	
	/*
	 * 修改教师信息
	 */
	public int alterTeacherInformation(Teacher teacher) {
		String sql="update teachersInformation set teacherName='"+teacher.getTeacherName()+"',sex='"+teacher.getSex()+"',"
				+ "dept='"+teacher.getDept()+"',teacherId='"+teacher.getTeacherId()+"',"
						+ "pwd='"+teacher.getPwd()+"',tel='"+teacher.getTel()+"' where teacherNo='"+teacher.getTeacherNo()+"'";
		return new OracleHelper().executeUpdate(sql);
		
	}
	
	/*
	 * 获取一共有多少条教师信息
	 */
	public int queryRecordCount() {
		String sql = "select count(*) from teachersInformation";
		Object obj = new OracleHelper().executeSingleQuery(sql);
		return Integer.parseInt(obj.toString());
	}
	
	/*
	 * 通过教师号查询某教师所有信息
	 */
	public Teacher queryTeacherByNo(String teacherNo) {
		Teacher teacher = null;
		try {
			String sql = "select * from teachersInformation where teacherNo='" + teacherNo + "'";
			ResultSet rs = new OracleHelper().executeQuery(sql);
			while(rs.next()) {
			String teacherno = rs.getString(1);
			String name = rs.getString(2);
			String sex = rs.getString(3);
			String dept = rs.getString(4);
			String teacherId = rs.getString(5);
			String pwd = rs.getString(6);
			String tel = rs.getString(7);
			teacher = new Teacher(teacherno, name, sex, dept, teacherId, pwd, tel);
			}
			OracleHelper.closeConnection();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return teacher;
	}

	/*
	 * 显示所有教师信息
	 */
	public Object[][] showAllTeachers() {
		int nums = queryRecordCount();
		Object[][] teachers = new Object[nums + 1][7];
		try {
			String sql = "select * from teachersInformation";
			ResultSet rs = new OracleHelper().executeQuery(sql);
			while (rs.next()) {
				teachers[nums][0] = rs.getString(1);
				teachers[nums][1] = rs.getString(2);
				teachers[nums][2] = rs.getString(3);
				teachers[nums][3] = rs.getString(4);
				teachers[nums][4] = rs.getString(5);
				teachers[nums][5] = rs.getString(6);
				teachers[nums][6] = rs.getString(7);
				nums++;
			}
			OracleHelper.closeConnection();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return teachers;

	}
	
	/**
	 * 分页显示所有教师的信息
	 */
	public List<Teacher> showAllTeachersByPage(int page){
		List<Teacher> tlist=new ArrayList<Teacher>();
		Connection conn=getConnection();
		String sql="select teacherNo,teacherName,sex,dept,teacherId,pwd,tel from(\r\n" + 
				"select teacherNo,teacherName,sex,dept,teacherId,pwd,tel,rownum as num from teachersInformation)\r\n" + 
				"where num between ? and ?";
		try {
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, (page-1)*Teacher.PAGE_SIZE+1);
			ps.setInt(2, page*Teacher.PAGE_SIZE);
			ResultSet rs=ps.executeQuery();
			while(rs.next()) {
				Teacher teacher=new Teacher();
				teacher.setTeacherNo(rs.getString(1));
				teacher.setTeacherName(rs.getString(2));
				teacher.setSex(rs.getString(3));
				teacher.setDept(rs.getString(4));
				teacher.setTeacherId(rs.getString(5));
				teacher.setPwd(rs.getString(6));
				teacher.setTel(rs.getString(7));
				tlist.add(teacher);
			}
			rs.close();
			ps.close();
			conn.close();
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return tlist;
		
	}

}
